/*------------------<-- Start of Description -->---------------------\ | Select variables names from a dataset, use for SELECT statement in | | PROC SQL; | |--------------------<-- End of Description -->----------------------| |--------------------------------------------------------------------| |-----------<-- Start of Files or Arguements Needed-->---------------| | Arguments: | | indata - the input dataset; | | dalias - the alias of the input dataset; | | var - the name of variables you want to select; | | except - the number of variables you don't want to select; | |------------<-- End of Files or Arguements Needed -->---------------| |--------------------------------------------------------------------| |------------------<-- Start of Files Created -->--------------------| | Example 1: | | 1) %let x=%selectvar(var=test xtest and stupid); | | %let y=%selectvar(indata=attest.covar, except=atp THERA); | | %put x = &x., y = &y..; | | 2) | | proc sql; | | create table temp as | | select %selectvar(indata=attest.monster, dalias=m), | | %selectvar(indata=attest.covar, dalias=c, | | except=atp MVD commnt1-commnt10) | | from attest.covar as c, attest.monster as m; | | quit; | | Example 2: | | proc sql; | | select %selectvar(acedcrf.implant, a, pt serialno) | | from acedcrf.implant as a; | | quit; | | Usage: %selectvar(indata=, dalias, var=, except=); | \------------------<-- Start of Files Created -->-------------------*/ %macro selectvar(indata=, dalias=, var=, except=)/parmbuff; /*--------------------------------------------\ | Copy Right: Duo Zhou; | | Created: 11-3-2001 10:52pm; | | Modified: 5-9-2002 7:15pm; | | Purpose: Select variable names from a data;| \--------------------------------------------*/ %local nvars var vars numvars nargs argtmp argmnt1 argmnt2 argmnt3 argmnt4 currvar _indsid_ _invari_ _innvars_ _invtype_ _convar_ _invrc_ _excptnvars_ _conexcpt_ _excpti_ _excptvtype_; %do i=1 %to 2; %let argtmp=%qscan(%quote(&syspbuff),&i, %str(,())); %if (not %index(%quote(%upcase(%sysfunc(compress(%quote(&argtmp))))),%quote(%upcase(=)))) %then %do; %if &i eq 1 %then %let indata=&argtmp; %else %if &i=2 %then %let dalias=&argtmp; %end; %end; %if (%length(&var) gt 1) %then %do; /* Add some code to change the format of var10-var20 to var10 var11 var12 ... var20*/ %if (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&var))))), %str(%()) and %index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&var))))), %str(%()) lt 2) %then %do; %let var=%substr(%quote(%trim(%left(&var))), 2, %eval(%length(%quote(%trim(%left(&var))))-2)); %let var=%sysfunc(translate(%quote(&var), ' ', ',')); %end; %local _tmpvari_ _tmpivar_ _sub1var_ _tmp1var_ _tmp2var_ _vartmp_ _convar_ _locali_; %if (%index(%quote(&var), %quote(,))) %then %let _vardlm_=%quote(,); %else %let _vardlm_=%quote( ); %if (%sysfunc(index(%quote(&var),_ALL_))) %then %let var=*; %else %if (%sysfunc(index(%quote(%upcase(&var)),_CHARACTER_))) %then %do; %if (%sysfunc(exist(&indata))) %then %do; %let _indsid_=%sysfunc(open(&indata)); %let _innvars_=%sysfunc(attrn(&_indsid_,NVARS)); %let _convar_=; %do _invari_=1 %to &_innvars_; %let _invtype_=%sysfunc(vartype(&_indsid_, &_invari_)); %if (not %index(%quote(%upcase(&_invtype_)),%quote(N))) %then %do; %let _convar_= %trim(%left(&_convar_)) %sysfunc(varname(&_indsid_,&_invari_)); %end; %end; %let _invrc_=%sysfunc(close(&_indsid_)); %let var=&_convar_; %end; %end; %else %if (%sysfunc(index(%quote(%upcase(&var)),_NUMERIC_))) %then %do; %if (%sysfunc(exist(&indata))) %then %do; %let _indsid_=%sysfunc(open(&indata)); %let _innvars_=%sysfunc(attrn(&_indsid_,NVARS)); %let _convar_=; %do _invari_=1 %to &_innvars_; %let _invtype_=%sysfunc(vartype(&_indsid_, &_invari_)); %if (%index(%quote(&_invtype_),%quote(N))) %then %do; %let _convar_= %trim(%left(&_convar_)) %sysfunc(varname(&_indsid_,&_invari_)); %end; %end; %let _invrc_=%sysfunc(close(&_indsid_)); %let var=&_convar_; %end; %end; %else %do; %let _convar_=; %do %while(%index(%quote(&var), %str(-))); %let _sub1var_=%substr(%quote(&var), 1, %eval(%index(%quote(&var), %str(-))-1)); %let var=%substr(%quote(&var), %eval(%index(%quote(&var), %str(-))+1), %eval(%length(&var)-%index(%quote(&var), %str(-)))); %let _tmpvari_=0; %do %while(%length(%nrbquote(%scan(%nrbquote(&_sub1var_), %eval(&_tmpvari_+1), %nrbquote(&_vardlm_))))); %let _tmpvari_=%eval(&_tmpvari_+1); %let _tmp1var_=%nrbquote(%qscan(%nrbquote(&_sub1var_), &_tmpvari_, %%nrbquote(&_vardlm_))); %if (%length(%nrbquote(%scan(%nrbquote(&_sub1var_), %eval(&_tmpvari_+1), %nrbquote(&_vardlm_))))) %then %do; %if (%quote(&_convar_) eq) %then %let _convar_=%trim(%quote(%left(%quote(&_tmp1var_)))); %else %let _convar_=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%quote(%left(%quote(&_tmp1var_)))); %end; %else %if ( %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1var_)))+1)) %then %do; %let _arrvarbeg_=%substr(%quote(&_tmp1var_), %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1var_)))+1), %eval(%length(&_tmp1var_)- %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1var_)))))); %let _arrvarbroot_=%substr(%quote(&_tmp1var_), 1, %sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1var_)))); %if (%quote(&var) ne) %then %do; %let _tmp2var_=%nrbquote(%qscan(%nrbquote(&var), 1, %%nrbquote(&_vardlm_))); %if (%sysfunc(rxmatch(%sysfunc(rxparse($d $s)),&_tmp2var_))) %then %do; %let _arrvarend_=%substr(%quote(&_tmp2var_), %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2var_)))+1), %eval(%length(&_tmp2var_)- %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2var_)))))); %let _arrvareroot_=%substr(%quote(&_tmp2var_), 1,%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2var_)))); %if (%quote(&_arrvarbroot_) eq %quote(&_arrvareroot_)) %then %do; %do _locali_=&_arrvarbeg_ %to %eval(&_arrvarend_-1); %if (%quote(&_convar_) eq) %then %let _convar_=%trim(%left(&_arrvarbroot_))%trim(%left(&_locali_)); %else %let _convar_=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%left(&_arrvarbroot_))%trim(%left(&_locali_)); %end; %end; %else %do; %if (%quote(&_convar_) eq) %then %let _convar_=%trim(%quote(%left(%quote(&_tmp1var_)))); %else %let _convar_=%trim(%quote(%left(&_convar_)))%quote(&_vardlm_)%trim(%quote(%left(%quote(&_tmp1var_)))); %put ==> Alert! Variable name &_tmp1var_ and &_tmp2var_ do not have the same pattern!; %end; %end; %else %do; %if (%quote(&_convar_) eq) %then %let _convar_=%trim(%quote(%left(%quote(&_tmp1var_)))); %else %let _convar_=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%quote(%left(%quote(&_tmp1var_)))); %put ==> Alert! Variable name &_tmp2var_ does not have a numeric suffix!; %end; %end; %else %do; %if (%quote(&_convar_) eq) %then %let _convar_=%trim(%quote(%left(%quote(&_tmp1var_)))); %else %let _convar_=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%quote(%left(%quote(&_tmp1var_)))); %put ==> Alert! No variable names are provided after &_tmp1var_ -!; %end; %end; %else %do; %if (%quote(&_convar_) eq) %then %let _convar_=%trim(%quote(%left(%quote(&_tmp1var_)))); %else %let _convar_=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%quote(%left(%quote(&_tmp1var_)))); %put ==> Alert! Variable name &_tmp1var_ does not have a numeric suffix!; %end; %end; %end; %let var=%trim(%quote(%left(%quote(&_convar_))))%quote(&_vardlm_)%trim(%quote(%left(%quote(&var)))); %end; /* End of Add */ %let vars=; %let nvars=1; %let var1=%qscan(&var, &nvars, %str( )); %if (%length(&dalias)>0) and (%quote(&dalias) ne) %then %do; %let vars=&dalias..&var1; %end; %else %if (not %index(&indata, %quote(.))) and (%quote(&indata) ne) %then %do; %let vars=&indata..&var1; %end; %else %do; %let vars=&var1; %end; %do %while(%length(&&var&nvars)>1); %let nvars=%eval(&nvars+1); %let var&nvars=%qscan(&var, &nvars, %str( )); %if &&var&nvars ne %then %do; %if (%length(&dalias)>0) and (%quote(&dalias) ne) %then %do; %let vars=&vars, &dalias..&&var&nvars; %end; %else %if (not %index(&indata, %quote(.))) and (%quote(&indata) ne) %then %do; %let vars=&vars, &indata..&&var&nvars; %end; %else %do; %let vars=&vars, &&var&nvars; %end; %end; %end; %let nvars=&nvars-1; %end; %else %do; %if (%index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&except))))), %str(%()) and %index(%BQUOTE(%trim(%BQUOTE(%left(%BQUOTE(&except))))), %str(%()) lt 2) %then %do; %let except=%substr(%quote(%trim(%left(&except))), 2, %eval(%length(%quote(%trim(%left(&except))))-2)); %let except=%sysfunc(translate(%quote(&except), ' ', ',')); %end; /* Add some code to change the format of excpt10-excpt20 to excpt10 excpt11 excpt12 ... excpt20*/ %local _tmpexcpti_ _tmpiexcpt_ _sub1excpt_ _tmp1excpt_ _tmp2excpt_ _excpttmp_ _conexcpt_ _localj_; %if (%index(%quote(&except), %quote(,))) %then %let _excptdlm_=%quote(,); %else %let _excptdlm_=%quote( ); %if (%sysfunc(index(%quote(&except),_ALL_))) %then %let except=*; %else %if (%sysfunc(index(%quote(%upcase(&except)),_CHARACTER_))) %then %do; %if (%sysfunc(exist(&indata))) %then %do; %let _indsid_=%sysfunc(open(&indata)); %let _excptnvars_=%sysfunc(attrn(&_indsid_,NVARS)); %let _conexcpt_=; %do _excpti_=1 %to &_excptnvars_; %let _excptvtype_=%sysfunc(vartype(&_indsid_, &_excpti_)); %if (not %index(%quote(%upcase(&_excptvtype_)),%quote(N))) %then %do; %let _conexcpt_= %trim(%left(&_conexcpt_)) %sysfunc(varname(&_indsid_,&_excpti_)); %end; %end; %let _invrc_=%sysfunc(close(&_indsid_)); %let except=&_conexcpt_; %end; %end; %else %if (%sysfunc(index(%quote(%upcase(&except)),_NUMERIC_))) %then %do; %if (%sysfunc(exist(&indata))) %then %do; %let _indsid_=%sysfunc(open(&indata)); %let _excptnvars_=%sysfunc(attrn(&_indsid_,NVARS)); %let _conexcpt_=; %do _excpti_=1 %to &_excptnvars_; %let _excptvtype_=%sysfunc(vartype(&_indsid_, &_excpti_)); %if (%index(%quote(%upcase(&_excptvtype_)),%quote(N))) %then %do; %let _conexcpt_= %trim(%left(&_conexcpt_)) %sysfunc(varname(&_indsid_,&_excpti_)); %end; %end; %let _invrc_=%sysfunc(close(&_indsid_)); %let except=&_conexcpt_; %end; %end; %else %do; %let _conexcpt_=; %do %while(%index(%quote(&except), %str(-))); %let _sub1excpt_=%substr(%quote(&except), 1, %eval(%index(%quote(&except), %str(-))-1)); %let except=%substr(%quote(&except), %eval(%index(%quote(&except), %str(-))+1), %eval(%length(&except)-%index(%quote(&except), %str(-)))); %let _tmpexcpti_=0; %do %while(%length(%nrbquote(%scan(%nrbquote(&_sub1excpt_), %eval(&_tmpexcpti_+1), %nrbquote(&_excptdlm_))))); %let _tmpexcpti_=%eval(&_tmpexcpti_+1); %let _tmp1excpt_=%nrbquote(%qscan(%nrbquote(&_sub1excpt_), &_tmpexcpti_, %%nrbquote(&_excptdlm_))); %if (%length(%nrbquote(%scan(%nrbquote(&_sub1excpt_), %eval(&_tmpexcpti_+1), %nrbquote(&_excptdlm_))))) %then %do; %if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%quote(%left(%quote(&_tmp1excpt_)))); %else %let _conexcpt_=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&_tmp1excpt_)))); %end; %else %if ( %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1excpt_)))+1)) %then %do; %let _arrexcptbeg_=%substr(%quote(&_tmp1excpt_), %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1excpt_)))+1), %eval(%length(&_tmp1excpt_)- %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1excpt_)))))); %let _arrexcptbroot_=%substr(%quote(&_tmp1excpt_), 1, %sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp1excpt_)))); %if (%quote(&except) ne) %then %do; %let _tmp2excpt_=%nrbquote(%qscan(%nrbquote(&except), 1, %%nrbquote(&_excptdlm_))); %if (%sysfunc(rxmatch(%sysfunc(rxparse($d $s)),&_tmp2excpt_))) %then %do; %let _arrexcptend_=%substr(%quote(&_tmp2excpt_), %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2excpt_)))+1), %eval(%length(&_tmp2excpt_)- %eval(%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2excpt_)))))); %let _arrexcpteroot_=%substr(%quote(&_tmp2excpt_), 1,%sysfunc(rxmatch(%sysfunc(rxparse($- ($a|_))),%quote(&_tmp2excpt_)))); %if (%quote(&_arrexcptbroot_) eq %quote(&_arrexcpteroot_)) %then %do; %do _localj_=&_arrexcptbeg_ %to %eval(&_arrexcptend_-1); %if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%left(&_arrexcptbroot_))%trim(%left(&_localj_)); %else %let _conexcpt_=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%left(&_arrexcptbroot_))%trim(%left(&_localj_)); %end; %end; %else %do; %if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%quote(%left(%quote(&_tmp1excpt_)))); %else %let _conexcpt_=%trim(%quote(%left(&_conexcpt_)))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&_tmp1excpt_)))); %put ==> Alert! excptiable name &_tmp1excpt_ and &_tmp2excpt_ do not have the same pattern!; %end; %end; %else %do; %if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%quote(%left(%quote(&_tmp1excpt_)))); %else %let _conexcpt_=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&_tmp1excpt_)))); %put ==> Alert! excptiable name &_tmp2excpt_ does not have a numeric suffix!; %end; %end; %else %do; %if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%quote(%left(%quote(&_tmp1excpt_)))); %else %let _conexcpt_=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&_tmp1excpt_)))); %put ==> Alert! No excptiable names are provided after &_tmp1excpt_ -!; %end; %end; %else %do; %if (%quote(&_conexcpt_) eq) %then %let _conexcpt_=%trim(%quote(%left(%quote(&_tmp1excpt_)))); %else %let _conexcpt_=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&_tmp1excpt_)))); %put ==> Alert! excptiable name &_tmp1excpt_ does not have a numeric suffix!; %end; %end; %end; %let except=%trim(%quote(%left(%quote(&_conexcpt_))))%quote(&_excptdlm_)%trim(%quote(%left(%quote(&except)))); %end; /* End of Add */ %let dsid=%sysfunc(open(&indata)); %let vars=; %if &dsid %then %do; %let nvars=%sysfunc(attrn(&dsid,NVARS)); %let numvars=0; %if &nvars>=1 %then %do; %do _i_=1 %to &nvars; %let var&_i_=%sysfunc(varname(&dsid,&_i_)); %if (%sysfunc(indexw(%upcase(&except),%upcase(&&var&_i_))) lt 1) %then %do; %let numvars=%eval(&numvars+1); %if &numvars = 1 %then %do; %if (%length(&dalias)>0) and (%quote(&dalias) ne) %then %do; %let vars=&dalias..&&var&_i_; %end; %else %if (not %index(&indata, %quote(.))) and (%quote(&indata) ne) %then %do; %let vars=&indata..&&var&_i_; %end; %else %do; %let vars=&&var&_i_; %end; %end; %else %do; %if (%length(&dalias)>0) and (%quote(&dalias) ne) %then %do; %let vars=&vars, &dalias..&&var&_i_.; %end; %else %if (not %index(&indata, %quote(.))) and (%quote(&indata) ne) %then %do; %let vars=&vars, &indata..&&var&_i_.; %end; %else %do; %let vars=&vars, &&var&_i_; %end; %end; %end; %end; %end; %else %if &nvars<1 %then %do; %put ==> Alert! Data "&indata" has no variables. ; %end; %let rc=%sysfunc(close(&dsid)); %end; %else %do; %put ==> Alert! Data "&indata" is invalid. ; %end; %end;&vars %put; %mend selectvar;